﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace APPWG4806.Haper
{
    public class DatabaseHelper
    {
        private static string connectionString = "Server=172.23.232.65;Database=MGE_CarLine;uid=sa;Password=#data*8864;";


        /// <summary>
        /// 执行查询并返回 DataTable
        /// </summary>
        /// <param name="sqlQuery">sql语句</param>
        /// <param name="parameters">查询条件</param>
        /// <returns>DataTable，包含查询结果。</returns>
        public static DataTable ExecuteQuery(string sqlQuery, List<SqlParameter> parameters = null)
        {
            DataTable dataTable = new DataTable();
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        adapter.Fill(dataTable);
                    }
                }
            }
            return dataTable;
        }


        // 执行分页查询并返回 DataTable
        /// <summary>
        /// 根据条件查询并且分页
        /// </summary>
        /// <param name="sqlQuery">SQL语句</param>
        /// <param name="pageIndex">第几页</param>
        /// <param name="pageSize">一页多少数据</param>
        /// <param name="parameters">查询条件参数</param>
        /// <returns></returns>
        /// <exception cref="InvalidOperationException"></exception>
        public static DataTable ExecuteQuery(string sqlQuery, int pageIndex, int pageSize, List<SqlParameter> parameters = null)
        {
            DataTable dataTable = new DataTable();
            int offset = (pageIndex - 1) * pageSize;

            // 检查 SQL 查询是否包含 ORDER BY 子句
            bool hasOrderBy = sqlQuery.IndexOf("ORDER BY",StringComparison.OrdinalIgnoreCase)>=0;
            

            // 如果包含 ORDER BY 子句，则添加分页参数
            if (hasOrderBy && pageIndex > 0 && pageSize > 0)
            {
                sqlQuery += " OFFSET " + offset + " ROWS FETCH NEXT " + pageSize + " ROWS ONLY";
            }
            else if (!hasOrderBy && pageIndex > 0 && pageSize > 0)
            {
                // 如果没有 ORDER BY 子句，但提供了分页参数，则需要添加
                throw new InvalidOperationException("SQL 查询必须包含 ORDER BY 子句以使用分页。");
            }

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                    }
                    using (SqlDataAdapter adapter = new SqlDataAdapter(command))
                    {
                        adapter.Fill(dataTable);
                    }
                }
            }
            return dataTable;
        }


        /// <summary>
        /// 执行非查询命令（如 INSERT、UPDATE、DELETE）
        /// </summary>
        /// <param name="sqlCommand">sql语句</param>
        /// <param name="parameters">查询条件</param>
        /// <returns>int，受影响的行数。大于0表示操作成功</returns>
        public static int ExecuteNonQuery(string sqlCommand, List<SqlParameter> parameters = null)
        {
            int result = 0;
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sqlCommand, connection))
                {
                    if (parameters != null)
                    {
                        command.Parameters.AddRange(parameters.ToArray());
                    }
                    result = command.ExecuteNonQuery();
                }
            }
            return result;
        }


        /// <summary>
        /// 查询表总数量
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件</param>
        /// <param name="parameters">查询参数</param>
        /// <returns></returns>
        public static int GetRowCount(string tableName, string condition, SqlParameter[] parameters)
        {
            string query = $"SELECT COUNT(*) FROM {tableName} WHERE {condition}";
            int rowCount = 0;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(query, connection))
                {
                    if (parameters!=null)
                    {
                        command.Parameters.AddRange(parameters);
                    }
                    rowCount = Convert.ToInt32(command.ExecuteScalar());
                }
            }
            return rowCount;
        }


        /// <summary>
        /// 按照写的SQL语句查询有多少条总记录数
        /// </summary>
        /// <param name="sqlQuery"></param>
        /// <returns></returns>
        public static int GetRecordCount(string sqlQuery)
        {
            int rowCount = 0;

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(sqlQuery, connection))
                {
                    rowCount = Convert.ToInt32(command.ExecuteScalar());
                }
            }
            return rowCount;
        }

        /// <summary>
        /// 根据某条件批量修改
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columns">修改字段</param>
        /// <param name="conditions">修改条件</param>
        /// <param name="parameters">批量修改条件</param>
        /// <returns>int，受影响的行数。大于0表示操作成功</returns>
        public static int UpdateWhere(string tableName, string columns, string conditions, List<SqlParameter> parameters)
        {
            string commandText = $"UPDATE {tableName} SET {columns} WHERE {conditions}";
            return ExecuteNonQuery(commandText, parameters);
        }

        // 根据条件批量删除
        // int，受影响的行数。大于0表示操作成功
        public static int DeleteWhere(string tableName, string conditions, List<SqlParameter> parameters)
        {
            string commandText = $"DELETE FROM {conditions}";
            return ExecuteNonQuery(commandText, parameters);
        }

        // 批量添加
        // int，受影响的行数。大于0表示操作成功
        public static int InsertBulk(string tableName, DataTable dataTable)
        {
            StringBuilder columns = new StringBuilder();
            StringBuilder values = new StringBuilder();
            for (int i = 0; i < dataTable.Columns.Count; i++)
            {
                columns.Append(dataTable.Columns[i].ColumnName);
                if (i < dataTable.Columns.Count - 1)
                {
                    columns.Append(", ");
                }
            }
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                values.Append("(");
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    values.Append("@" + dataTable.Columns[j].ColumnName + i);
                    if (j < dataTable.Columns.Count - 1)
                    {
                        values.Append(", ");
                    }
                }
                values.Append("),");
            }
            values.Length--; // Remove the last comma
            string commandText = $"INSERT INTO {tableName} ({columns}) VALUES ({values})";
            List<SqlParameter> parameters = new List<SqlParameter>();
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    parameters.Add(new SqlParameter("@" + dataTable.Columns[j].ColumnName + i, dataTable.Rows[i][j]));
                }
            }
            return ExecuteNonQuery(commandText, parameters);
        }
    }


    /*
     * 使用手册
        // 执行查询
        string query = "SELECT * FROM MyTable";
        DataTable dataTable = DatabaseHelper.ExecuteQuery(query);

        // 执行非查询命令
        string commandText = "UPDATE MyTable SET ColumnName = @Value WHERE ID = @ID";
        List<SqlParameter> parameters = new List<SqlParameter>
        {
            new SqlParameter("@Value", SqlDbType.VarChar) { Value = "NewValue" },
            new SqlParameter("@ID", SqlDbType.Int) { Value = 1 }
        };
        int affectedRows = DatabaseHelper.ExecuteNonQuery(commandText, parameters);

        // 执行带参数的查询
        string queryWithParams = "SELECT * FROM MyTable WHERE ID = @ID";
        parameters = new List<SqlParameter>
        {
            new SqlParameter("@ID", SqlDbType.Int) { Value = 1 }
        };
        DataTable result = DatabaseHelper.ExecuteQueryWithParameters(queryWithParams, parameters);
     */
}
